﻿using Groep2TINGHService.Get_Set_Classes;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Groep2TINGHService.DB_Classes
{
    public class ContractDB
    {
        //Geeft een lijst terug met alle Contracts
        public static List<Contract> GetAllContracts()
        {
            List<Contract> lijst = new List<Contract>();
            SqlConnection connection = ConnectionDB.GetConnection();
            SqlCommand selectcommand = new SqlCommand("SELECT con.Id FROM Company com, Contract con WHERE com.Id = con.CompanyId", connection);
            try
            {
                Contract temp;
                connection.Open();
                SqlDataReader reader = selectcommand.ExecuteReader();
                int Id = reader.GetOrdinal("Id");

                while (reader.Read())
                {
                    temp = new Contract();
                    temp.Id= reader.GetGuid(Id);
                    lijst.Add(temp);

                }
                reader.Close();
                return lijst;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        //Invoegen contract
        public static int InsertContract(Contract contract, ContractFormula formula, Company company)
        {
            SqlConnection connection = ConnectionDB.GetConnection();
            SqlCommand insertCommand = new SqlCommand("Proc_insertContract", connection);
            insertCommand.CommandType = CommandType.StoredProcedure;
            insertCommand.Parameters.AddWithValue("@StartDate", contract.StartDate);
            insertCommand.Parameters.AddWithValue("@EndDate", contract.EndDate);
            insertCommand.Parameters.AddWithValue("@Name", company.Name);
            insertCommand.Parameters.AddWithValue("@Description", formula.Description);
            try
            {
                connection.Open();
                int x = insertCommand.ExecuteNonQuery();
                return x;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }
        
        //Wijzigen van Contract Status
        public static bool ModifyContractRunning(Company company)
        {
            SqlConnection connection = ConnectionDB.GetConnection();
            string modify = "UPDATE Company " +
                            "SET Street=@Street, Zipcode=@Zipcode, City=@City, Country=@Country, Email=@Email, Phone=@Phone, Employees=@Employees " +
                            "WHERE Name = @Name";
            SqlCommand modifyCommand = new SqlCommand(modify, connection);
            modifyCommand.Parameters.AddWithValue("@Name", company.Name);
            modifyCommand.Parameters.AddWithValue("@Street", company.Street);
            modifyCommand.Parameters.AddWithValue("@Zipcode", company.ZipCode);
            modifyCommand.Parameters.AddWithValue("@City", company.City);
            modifyCommand.Parameters.AddWithValue("@Country", company.Country);
            modifyCommand.Parameters.AddWithValue("@Email", company.Email);
            modifyCommand.Parameters.AddWithValue("@Phone", company.Phone);
            modifyCommand.Parameters.AddWithValue("@Employees", company.Employees);


            try
            {
                connection.Open();
                int count = modifyCommand.ExecuteNonQuery();
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }
        public static Contract GetContractByCompany(string contract)
        {
            Contract temp = new Contract();
            Company company = new Company();
            ContractFormula formula = new ContractFormula();
            SqlConnection connection = ConnectionDB.GetConnection();
            string select = "SELECT con.Id, com.Name, con.StartDate, con.EndDate, form.Description FROM Company com, Contract con, ContractFormula form WHERE con.Id = @Id AND com.Id = Con.CompanyId AND con.ContractFormulaId = form.Id";
            SqlCommand SelectCommand = new SqlCommand(select, connection);
            SelectCommand.Parameters.AddWithValue("@Id", contract);
           
           try
            {
                connection.Open();
                SqlDataReader reader = SelectCommand.ExecuteReader();
                if (reader.Read())
                {
                    int StartDate = reader.GetOrdinal("StartDate");
                    int EndDate = reader.GetOrdinal("EndDate");
                    int Name = reader.GetOrdinal("Name");
                    int Description = reader.GetOrdinal("Description");
                    
                    temp.StartDate = reader.GetDateTime(StartDate);
                    temp.EndDate = reader.GetDateTime(EndDate);
                    company.Name = reader.GetString(Name);
                    formula.Description = reader.GetString(Description);
                }
                reader.Close();
                //int x = SelectCommand.ExecuteNonQuery();
               
               return temp;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

        }
        public static Company GetContractByCompanyWithName(string contract)
        {
            Company company = new Company();
            SqlConnection connection = ConnectionDB.GetConnection();
            string select = "SELECT con.Id, com.Name, con.StartDate, con.EndDate, form.Description FROM Company com, Contract con, ContractFormula form WHERE con.Id = @Id AND com.Id = Con.CompanyId AND con.ContractFormulaId = form.Id";
            SqlCommand SelectCommand = new SqlCommand(select, connection);
            SelectCommand.Parameters.AddWithValue("@Id", contract);

            try
            {
                connection.Open();
                SqlDataReader reader = SelectCommand.ExecuteReader();
                if (reader.Read())
                {
                    int StartDate = reader.GetOrdinal("StartDate");
                    int EndDate = reader.GetOrdinal("EndDate");
                    int Name = reader.GetOrdinal("Name");
                    int Description = reader.GetOrdinal("Description");

                    company.Name = reader.GetString(Name);

                }
                reader.Close();
                //int x = SelectCommand.ExecuteNonQuery();

                return company;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }
        public static ContractFormula GetContractByCompanyWithDescription(string contract)
        {
            ContractFormula formula = new ContractFormula();
            SqlConnection connection = ConnectionDB.GetConnection();
            string select = "SELECT con.Id, com.Name, con.StartDate, con.EndDate, form.Description FROM Company com, Contract con, ContractFormula form WHERE con.Id = @Id AND com.Id = Con.CompanyId AND con.ContractFormulaId = form.Id";
            SqlCommand SelectCommand = new SqlCommand(select, connection);
            SelectCommand.Parameters.AddWithValue("@Id", contract);

            try
            {
                connection.Open();
                SqlDataReader reader = SelectCommand.ExecuteReader();
                if (reader.Read())
                {
                    int StartDate = reader.GetOrdinal("StartDate");
                    int EndDate = reader.GetOrdinal("EndDate");
                    int Name = reader.GetOrdinal("Name");
                    int Description = reader.GetOrdinal("Description");

                    formula.Description = reader.GetString(Description);

                }
                reader.Close();
                //int x = SelectCommand.ExecuteNonQuery();

                return formula;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }
        public static int DeleteContract(string contract)
        {
            SqlConnection connection = ConnectionDB.GetConnection();
            string delete = "DELETE FROM Contract WHERE Id = @Id";
            SqlCommand deleteCommand = new SqlCommand(delete, connection);
            deleteCommand.Parameters.AddWithValue("@Id", contract);

            try
            {
                connection.Open();
                int x = deleteCommand.ExecuteNonQuery();
                return x;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

    }
}